using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Configuration;

using PConsulting.DataSets;

namespace PConsulting.DataAccess.TimeRegistration
{
    public class Registration
    {
        private string connectionString;

        public Registration()
        {
            connectionString = ConfigurationManager.ConnectionStrings["PConsultingDB"].ConnectionString;
        }

        public DataSet SelectByProject(int projectId)
        {
            DataSet ds = SqlHelper.GetDataSet(connectionString, "Registration_SelectByProject", new SqlParameter("projectId", projectId));
            return ds;
        }

        public DataSet SelectByProjectMonth(int projectId, DateTime month)
        {
            DataSet ds = SqlHelper.GetDataSet(connectionString, "Registration_SelectByProjectMonth", new SqlParameter("projectId", projectId), new SqlParameter("month", month));
            return ds;
        }

        public DataSet SelectByUsernameMonth(string userName, DateTime month)
        {
            DataSet ds = SqlHelper.GetDataSet(connectionString, "Registration_SelectByUsernameMonth", new SqlParameter("userName", userName), new SqlParameter("month", month));
            return ds;
        }

        public void Insert(int projectId, DateTime registrationDate, double? hours100, double? hours150, double? hours200, DateTime? startHour, DateTime? endHour, string remarks, double? standbyCompensation, double? shiftCompensation, double? km)
        {
            List<SqlParameter> parameters = new List<SqlParameter>();
            parameters.Add(new SqlParameter("projectId", projectId));
            parameters.Add(new SqlParameter("registrationDate", registrationDate));
            if (hours100.HasValue) parameters.Add(new SqlParameter("hours100", hours100.Value));
            if (hours150.HasValue) parameters.Add(new SqlParameter("hours150", hours150.Value));
            if (hours200.HasValue) parameters.Add(new SqlParameter("hours200", hours200.Value));
            if (startHour.HasValue) parameters.Add(new SqlParameter("startHour", startHour.Value));
            if (endHour.HasValue) parameters.Add(new SqlParameter("endHour", endHour.Value));
            if (remarks != null) parameters.Add(new SqlParameter("remarks", remarks));
            if (standbyCompensation.HasValue) parameters.Add(new SqlParameter("standbyCompensation", standbyCompensation.Value));
            if (shiftCompensation.HasValue) parameters.Add(new SqlParameter("shiftCompensation", shiftCompensation.Value));
            if (km.HasValue) parameters.Add(new SqlParameter("km", km.Value));

            SqlHelper.ExecuteSql(connectionString, "Registration_Insert", parameters.ToArray());
        }

        public void Update(int registrationId, int projectId, DateTime registrationDate, double? hours100, double? hours150, double? hours200, DateTime? startHour, DateTime? endHour, string remarks, double? standbyCompensation, double? shiftCompensation, double? km)
        {
            List<SqlParameter> parameters = new List<SqlParameter>();
            parameters.Add(new SqlParameter("registrationId", registrationId));
            parameters.Add(new SqlParameter("projectId", projectId));
            parameters.Add(new SqlParameter("registrationDate", registrationDate));
            if (hours100.HasValue) parameters.Add(new SqlParameter("hours100", hours100.Value));
            if (hours150.HasValue) parameters.Add(new SqlParameter("hours150", hours150.Value));
            if (hours200.HasValue) parameters.Add(new SqlParameter("hours200", hours200.Value));
            if (startHour.HasValue) parameters.Add(new SqlParameter("startHour", startHour.Value));
            if (endHour.HasValue) parameters.Add(new SqlParameter("endHour", endHour.Value));
            if (remarks != null) parameters.Add(new SqlParameter("remarks", remarks));
            if (standbyCompensation.HasValue) parameters.Add(new SqlParameter("standbyCompensation", standbyCompensation.Value));
            if (shiftCompensation.HasValue) parameters.Add(new SqlParameter("shiftCompensation", shiftCompensation.Value));
            if (km.HasValue) parameters.Add(new SqlParameter("km", km.Value));
            
            SqlHelper.ExecuteSql(connectionString, "Registration_Update", parameters.ToArray());
        }

        /// <summary>
        /// delete including from-date, but excluding to-date
        /// </summary>
        /// <param name="projectId"></param>
        /// <param name="registrationDate"></param>
        public void Delete(int projectId, DateTime registrationDateFrom, DateTime registrationDateTo)
        {
            List<SqlParameter> parameters = new List<SqlParameter>();
            parameters.Add(new SqlParameter("projectId", projectId));
            parameters.Add(new SqlParameter("registrationDateFrom", registrationDateFrom));
            parameters.Add(new SqlParameter("registrationDateTo", registrationDateTo));

            SqlHelper.ExecuteSql(connectionString, "Registration_Delete", parameters.ToArray());
        }

        public void Lock(int projectId, DateTime month)
        {
            Lock(projectId, month, true);
        }

        public void Unlock(int projectId, DateTime month)
        {
            Lock(projectId, month, false);
        }

        private void Lock(int projectId, DateTime month, bool locking)
        {
            List<SqlParameter> parameters = new List<SqlParameter>();
            parameters.Add(new SqlParameter("projectId", projectId));
            parameters.Add(new SqlParameter("month", month));
            parameters.Add(new SqlParameter("lock", locking));

            SqlHelper.ExecuteSql(connectionString, "Registration_Lock", parameters.ToArray());
        }

        public tdsTimesheetOverview SelectOverviewByMonth(DateTime month)
        {
            DataSet ds = SqlHelper.GetDataSet(connectionString, "Registration_SelectOverview", new SqlParameter("month", month));
            tdsTimesheetOverview tds = new tdsTimesheetOverview();
            ds.Tables[0].TableName = "tblConsultants";
            ds.Tables[1].TableName = "tblProjects";
            tds.Merge(ds);
            return tds;
        }

        public tdsTimesheets SelectTimesheet(int projectId, DateTime month)
        {
            DataSet ds = SqlHelper.GetDataSet(connectionString, "Timesheet_Select", new SqlParameter("projectId", projectId), new SqlParameter("month", month));
            tdsTimesheets tds = new tdsTimesheets();
            ds.Tables[0].TableName = "tblTimesheets";
            ds.Tables[1].TableName = "tblTimesheetStatus";
            tds.Merge(ds);
            return tds;
        }

        public void UpdateTimesheet(int projectId, DateTime month, string status)
        {
            SqlHelper.ExecuteSql(connectionString, "Timesheet_Update", new SqlParameter("projectId", projectId), new SqlParameter("month", month), new SqlParameter("status", status));
        }
    }

}
